import xlwt
import pandas as pd
import numpy as np
def creatEmptyExcel(title,class_info,target_count):
    #创建新表
    wb=xlwt.Workbook(encoding='utf-8',style_compression=0)
    ws=wb.add_sheet('各教学班评价表',cell_overwrite_ok=True)
    #
    #修改样式
    title_style=xlwt.XFStyle()
    info_style=xlwt.XFStyle()

    # 居中
    cellalign=xlwt.Alignment()
    cellalign.horz=0x02
    cellalign.vert=0x01

    #标题字体
    titlefont=xlwt.Font()
    titlefont.name='黑体'
    titlefont.bold=True
    titlefont.height=10*20

    # 课程信息字体
    infofont=xlwt.Font()
    infofont.name='宋体'
    # infofont.bold=True
    infofont.height=9*20

    title_style.alignment=cellalign
    title_style.font=titlefont
    info_style.alignment=cellalign
    info_style.font=infofont

    tall_style = xlwt.easyxf('font:height 420')  # 设置行高

    #写入文档
    col_count=16+3*target_count-1
    ws.write_merge(0,0,0,col_count,title,title_style)
    ws.row(0).set_style(tall_style)
    ws.write_merge(1,1,0,col_count,class_info,info_style)
    return wb

def setStyle(color_num,number):
    '''

    :param color_num:色号
    :param number: 数字类型号
    :return:
    '''
    data_style=xlwt.XFStyle()

    # 居中
    cellalign=xlwt.Alignment()
    cellalign.horz=0x02
    cellalign.vert=0x01

    # 字体
    datafont=xlwt.Font()
    datafont.name='宋体'
    datafont.height=9*20

    #数字显示位数
    num_format_1 ='0'
    num_format_2='0.0'
    num_format_3='0.00'
    num_format=[0,num_format_1,num_format_2,num_format_3]

    #单元格颜色
    pattern1 = xlwt.Pattern()
    pattern1.pattern = xlwt.Pattern.NO_PATTERN
    # 47米黄色，22灰色，42黄绿色，31紫蓝色
    pattern_list=[pattern1,47,22,42,31]
    for i in range(1,5):
        pattern=xlwt.Pattern()
        pattern.pattern = xlwt.Pattern.SOLID_PATTERN
        pattern.pattern_fore_colour=pattern_list[i]
        pattern_list[i]=pattern

    # 设置边框
    borders=xlwt.Borders()
    borders.left = 1
    borders.right = 1
    borders.top = 1
    borders.bottom = 1

    data_style.alignment=cellalign
    data_style.font=datafont
    data_style.borders=borders
    data_style.pattern=pattern_list[color_num]
    if number!=0:
        data_style.num_format_str=num_format[number]
    return data_style

def makeExcelTitle(title,class_info,small_target_value,weight):
    target_count=len(small_target_value)
    final_weight =weight[1]
    usual_weight =weight[0]
    wb=creatEmptyExcel(title,class_info,target_count)
    ws = wb.get_sheet('各教学班评价表')
    # 事先创建好样式
    astyle=setStyle(1,0)
    bstyle=setStyle(1,3)


    left_text=['学号','姓名','课程编号','考试科目','考试状态','性别','留学生','学院','考试日期','班级','年级','备注','平时\n成绩\nA\na1=%.2f'%usual_weight,'期末\n成绩\nB\na2=%.2f'%final_weight,'总成绩']
    for i,item in enumerate(left_text):
        ws.write_merge(2,6,i,i,item,astyle)
        if i in [0,5,6]:
            ws.col(i).width=ws.col(1).width//2

    # 绘制右半部分
    start_col=15
    end_col=target_count*3+start_col-1
    ws.write_merge(2,2,start_col,end_col,'成绩记录',astyle)
    flag=start_col
    for i in range(target_count):
        ws.write_merge(3,3,flag,flag+2,'课程目标%s'%str(i+1),astyle)
        flag=flag+3

    flag=start_col
    temp=['A','B','达成度']
    for i in range(target_count):
        for j in range(len(temp)):
            if j==2:
               ws.write(4,flag+j,temp[j],astyle)
            else:
               ws.write(4, flag+j,temp[j]+str(i+1),astyle)
        flag=flag+3

    flag=start_col
    for i in range(target_count):
        ws.write_merge(5,5,flag,flag+1,'A%s目标值'%str(i+1),astyle)
        ws.write(5,flag+2,small_target_value[i][0],bstyle)
        flag+=3

    flag=start_col
    for i in range(target_count):
        ws.write_merge(6,6,flag,flag+1,'B%s目标值'%str(i+1),astyle)
        ws.write(6,flag+2,small_target_value[i][1],bstyle)
        flag+=3

    ws.write_merge(2,6,end_col+1,end_col+1,'总目标\n达成度',astyle)
    return wb,ws

def readList(full_grade_list):
    sheet_count=len(full_grade_list)
    corr_list=[]
    weight=[]
    for i in range(sheet_count):
        temp_list=[]
        weight.append(float(full_grade_list[i][2]))
        for j in range(3, len(full_grade_list[i]), 4):
            big_topic_list = full_grade_list[i][j]
            for k in range(3,len(big_topic_list)):
                temp=big_topic_list[k][3:]
                for index in range(len(temp)):
                    try:
                        temp[index]=float(temp[index])
                    except:
                        temp[index]=0
                temp_list.append(temp)
        corr_list.append(temp_list)
    return corr_list,weight

def regretGrades(full_grade_list):
    link_counts=len(full_grade_list)
    topic_list = []
    topic_full_grades=[]
    for i in range(link_counts):
        link_full_grade=float(full_grade_list[i][1])
        full_grades=[]
        for j in range(3,len(full_grade_list[i]),4):
             weight=float(full_grade_list[i][j][2])
             for item in full_grade_list[i][j][3:]:
                 temp=float(item[2])/float(item[1])
                 temp=temp*weight*link_full_grade
                 topic_list.append(temp)
                 full_grades.append(float(item[1]))
        topic_full_grades.append(full_grades)

    regret_grades=[]
    for i in topic_full_grades:
        temp=list(map(lambda x,y:x*y,i,topic_list))
        regret_grades.append(temp)
    return regret_grades

def getSmallTargetValue(regret_full_grades,corr_list):
    sheet_count=len(regret_full_grades)
    small_target_value=[]
    for i in range(sheet_count):
        temp=pd.DataFrame(corr_list[i])
        temp=np.array(temp).T
        temp=np.sum(temp*regret_full_grades[i],axis=1)
        small_target_value.append(list(temp))
    small_target_value=list(map(lambda x,y:(x,y),small_target_value[0],small_target_value[1]))
    return small_target_value

# import json
# path='full_grades.json'
# f = open(path)
# m = json.load(f)

# title='2018-2019第二学期 钢结构设计原理 目标达成度计算表'
# class_info='  课程：[CE30108]钢结构设计原理（建工、地下、安装方向）        上课班级：208211-002              任课教师：熊刚'
# # m就是c总返回的列表
# corr_list=readList(m)[0]
# weight=readList(m)[1]
# regret_full_grades=regretGrades(m)
# small_target_value=getSmallTargetValue(regret_full_grades,corr_list)
# wb=makeExcelTitle(title,class_info,small_target_value,weight)
# save_path = '目标达成度计算表.xls'
# wb.save(save_path)



